package com.MTR;

import java.sql.*;
import java.util.*;


public class DerbyWrapper {
	
	// Singleton pattern
	private static DerbyWrapper inst = null;
	private DerbyWrapper()
	{
	}
	public static DerbyWrapper instance()
	{
		if (inst == null)
		{
			inst = new DerbyWrapper();
			inst.load();
		}
		return inst;
	}
	// end singleton
	
	// SQL Connection
	private Connection conn = null;
	
	// define the driver to use 
    String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	
	// connection URL, assume database exist
	private String connectionURL = "jdbc:derby:db/mtrdb";
	
	
	public void load()
	{
		//   ## LOAD DRIVER SECTION ##
	      try{
	          /*
	          **  Load the Derby driver. 
	          **     When the embedded Driver is used this action start the Derby engine.
	          **  Catch an error and suggest a CLASSPATH problem
	           */
	          Class.forName(driver); 
	      } catch(java.lang.ClassNotFoundException e) {
	          System.err.print("ClassNotFoundException: ");
	          System.err.println(e.getMessage());
	          System.out.println("\n    >>> Please check your CLASSPATH variable   <<<\n");
	      }
	      
	}
	
	public void unload()
	{
		 if (driver.equals(driver)) {
             boolean gotSQLExc = false;
             try {
                DriverManager.getConnection("jdbc:derby:mtrdb;shutdown=true");
             } catch (SQLException se)  {	
                if ( se.getSQLState().equals("XJ015") ) {		
                   gotSQLExc = true;
                }
             }
             if (!gotSQLExc) {
             	  System.out.println("Database did not shut down normally");
             }  else  {
                System.out.println("Database shut down normally");	
             }  
          }
	}
	
	/*
	public boolean connect()
	{
	    try {
			this.conn = DriverManager.getConnection(connectionURL);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}		 
        //System.out.println("Connected to database ");

		return true;
	}
	
	public boolean disconnect()
	{
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	*/
	
	
	public TreeMap<Integer, String> loadTrains()
	{
		String sqlString = "SELECT * FROM TRAIN";
		
		Statement  s;
		ResultSet rs;
		TreeMap<Integer, String> ret = null;
		
		try {
			this.conn = DriverManager.getConnection(connectionURL);
			// create the statement
			s = conn.createStatement();
			// execute
			rs = s.executeQuery(sqlString);
			
			// create return value
			ret = new TreeMap<Integer, String>();
			
			// traverse
			while(rs.next())
			{
				int id = rs.getInt(1);
				String label = rs.getString(2);
				ret.put(id, label);
			}
			
			// close connection
			rs.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ret;

	}
	
	// append current log into database
	public void appendLog(MoteData d)
	{
		
	}
	
	public ArrayList<MoteData> queryLog()
	{
		String sqlString = "SELECT * FROM LOG ORDER BY SampleDate";
		
		Statement  s;
		ResultSet rs;
		ArrayList<MoteData> ret = null;
		
		try {
			this.conn = DriverManager.getConnection(connectionURL);
			// create the statement
			s = conn.createStatement();
			// execute
			rs = s.executeQuery(sqlString);
			
			// create return value
			ret = new ArrayList<MoteData>();
			
			// traverse
			while(rs.next())
			{
				int tid = rs.getInt(1);
				int nid = rs.getInt(2);
				double temp = rs.getDouble(3);
				java.util.Date samp =  new java.util.Date(rs.getTimestamp(4).getTime());
				java.util.Date dload =  new java.util.Date(rs.getTimestamp(5).getTime());
				
				MoteData md = new MoteData(tid, nid, temp, samp, dload);
				ret.add(md);
			}
			
			// close connection
			rs.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ret;

	}
	
	// for test only
	public void appendRandom()
	{
		String sqlString = "INSERT INTO LOG VALUES(?, ?, ?, ?, ?)";
		
		PreparedStatement  ps;
		
		try {
			this.conn = DriverManager.getConnection(connectionURL);
			// create the statement
			ps = conn.prepareStatement(sqlString);
			
			Calendar c = Calendar.getInstance();
			long now = c.getTimeInMillis();
			
			ps.setInt(1, 1);
			ps.setInt(2, 1);
			
			//add item randomly
			for(int i = 0; i < 10; i++)
			{
				// replace statement value
				ps.setDouble(3, java.lang.Math.random() * 80);
				ps.setTimestamp(4, new Timestamp(now));
				ps.setTimestamp(5, new Timestamp(now));
				ps.executeUpdate();
			}
			
			// close connection
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	
	public void loadSetting()
	{
		String sqlString = "SELECT * FROM SETTING";
		
		Statement  s;
		
		ResultSet rs;
		
		try {
			this.conn = DriverManager.getConnection(connectionURL);
			// create the statement
			s = conn.createStatement();
			// execute
			rs = s.executeQuery(sqlString);
			
			// assume length > 1; query the first one
			rs.next();
			Setting.portNumber = rs.getInt(1);
			Setting.baudRate = rs.getInt(2);
			Setting.warningTemp = rs.getInt(3);
			Setting.errorTemp = rs.getInt(4);
			rs.close();
			
			// close connection
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// save the setting to database
	public void saveSetting()
	{
		String sqlString = "UPDATE SETTING SET PortNumber=?,BaudRate=?,WarnTemp=?,ErrTemp=?";
		
		PreparedStatement  ps;
		
		try {
			this.conn = DriverManager.getConnection(connectionURL);
			// create the statement
			ps = conn.prepareStatement(sqlString);
			// replace statement value
			ps.setInt(1, Setting.portNumber);
			ps.setInt(2, Setting.baudRate);
			ps.setInt(3, Setting.warningTemp);
			ps.setInt(4, Setting.errorTemp);
			
			ps.executeUpdate();
			
			// close connection
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
	}
	


}
